package com.kb.design.mapper.postgreSQL;


import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.kb.design.dto.QueryChartDTO;
import com.kb.design.entity.DesignBonusDistribution;
import com.kb.design.entity.DesignDaily;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.Map;

/**
 * (CustomUser)表数据库访问层
 *
 * @author makejava
 * @since 2023-08-22 10:56:59
 */
@Mapper
public interface DesignBonusDistributionMapper extends BaseMapper<DesignBonusDistribution> {

    //    SELECT sum(b.pin_num) as pin, sum(b.bonus_total) as bonus FROM design_bonus_distribution b LEFT JOIN design_project p ON b.project_id = p.id WHERE  p.relevant_responsible_person && ARRAY[25331,23988] and  p.completion_time  BETWEEN '2025-06-01' AND '2025-06-30';
    @Select({
            "SELECT",
            "    SUM(b.pin_num) AS pin,",
            "    SUM(b.bonus_total) AS bonus",
            "FROM",
            "    design_bonus_distribution b",
            "LEFT JOIN",
            "    design_project p ON b.project_id = p.id",
            "WHERE",
            "    p.relevant_responsible_person && ARRAY[${relevantResponsiblePerson}]",
            "   AND p.life_cycle = TRUE AND p.completion_time BETWEEN #{start} AND #{end}"
    })
    public Map<String, Object> getProjectBonusAllocation(QueryChartDTO queryChartDTO);

    //SELECT count(1) FROM  design_project p  WHERE  p.relevant_responsible_person && ARRAY[25331,23988] and  p.completion_time  BETWEEN '2025-06-01' AND '2025-06-30' AND p.life_cycle = TRUE;
    @Select({
            "SELECT COUNT(1)",
            "FROM design_project p",
            "WHERE ",
            "   p.life_cycle = TRUE",
            "   AND p.relevant_responsible_person && ARRAY[${relevantResponsiblePerson}]",
            "    AND p.completion_time BETWEEN #{start} AND #{end}"
    })
    public Integer getProjectCount(QueryChartDTO queryChartDTO);

    @Select({
            "SELECT SUM(o.total_amount) AS orderAmount",
            "FROM design_project p left join design_order o on p.order_id = o.id",
            "WHERE ",
            "   p.life_cycle = TRUE",
            "   AND p.relevant_responsible_person && ARRAY[${relevantResponsiblePerson}]",
            "AND p.completion_time BETWEEN #{start} AND #{end} AND o.dept2 = #{salesArea}"
    })
    public BigDecimal getOrderBonus(QueryChartDTO queryChartDTO);
}

